{
 "metadata": {
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.0"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python390jvsc74a57bd01ee744fdee3946c2af2bdc58f3796819357ed068616caa870dc20aacb53d0af6",
   "display_name": "Python 3.9.0 64-bit ('venv': venv)",
   "language": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "source": [
    "使用下面的分段模块进行对应数据库操作\n"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 初始化数据库\n",
    "from dblib import *\n",
    "db_init()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 新增成员\n",
    "\n",
    "name = '胡景'\n",
    "\n",
    "#--------------#\n",
    "from dblib import *\n",
    "new_member(name)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "汪宇工作记录：\n2021-03-22：1、东圣磷复肥反馈；2、神农资源开户资料尽职调查；\n2021-03-23：1、接待远东租赁;2、东圣磷复肥反馈;3、神农资源开户资料尽职调查；4、联系贴现客户\n2021-03-24：1、宁通物流销户，后期重新开户；2、东圣磷复肥反馈；\n2021-03-25：1、完成东圣反馈；2、协助开立葛洲坝保函\n2021-03-26：中盈科技续授信；\n2021-03-29：1、中盈科技续授信；2、东圣，开元银监检查回复；\n2021-03-30：1、神农资源尽职调查；2、东圣，开元银监检查回复；3、开户延伸服务；4、处理兴发银企直连；\n2021-03-31：1、东圣、开元银监检查回复；2、神农资源尽职调查；3、回复涉外排查；\n2021-04-01：1、完成东圣、开元银监检查；2、完成神农资源尽职调查；3、苏鹏预约开户4、雅斯解除抵押用印；\n2021-04-02：1、 苏鹏科技上门开户；2、办理雅斯解除抵押；\n2021-04-06：1、完成大额报错；2、 准备苏鹏开户资料；3、配合处理顺天建材清收；\n2021-04-07：1、联系深泽建材，要求本月分期还款；2、沟通神农资源开户；\n2021-04-08：1、前往顺天建材张贴公告；2、回复东圣银监检查资料；3、拜访兴发股份；\n2021-04-09：1、拜访兴发集团；2、参加核保会。\n2021-04-12：1、督促深泽建材处置进度，4月底以前偿还本金5万；2、拜访兴发集团，收集授信资料；\n2021-04-13：1、 苏鹏开户；2、 产城融合汇报；3、 配合风险部处理风险户；\n2021-04-15：1、参加总行一季度公司业务会；2、参加分行托管业务会；\n2021-04-16：1、苏鹏报表录入，行业分类；\n2021-04-19：1、 准备兴和开票资料；2、联系浩元上门核实资料；\n2021-04-20：1、神农资源录入报表；2、苏鹏规模，行业修改；\n"
     ]
    }
   ],
   "source": [
    "# 新增周报记录\n",
    "from datetime import date\n",
    "## 客户经理\n",
    "# name = '吴然'\n",
    "name = '汪宇'\n",
    "# name = '胡景'\n",
    "# name = '杨佳琪'\n",
    "## 日期\n",
    "\n",
    "# date_ = date(2021, 4, 19)\n",
    "# date_ = date(2021, 4, 20)\n",
    "date_ = date(2021, 4, 21)\n",
    "# date_ = date(2021, 4, 22)\n",
    "# date_ = date(2021, 4, 23)\n",
    "\n",
    "## 工作记录\n",
    "info_ = '1、 兴和开票；'\n",
    "\n",
    "#---------------------------------------------------------\n",
    "from dblib import *\n",
    "new_report(member_name = name, date_ = date_, info_ = info_)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# test\n",
    "from dblib import *\n",
    "\n",
    "query = Report.select().where(Report.date == date(2021,4,21))\n",
    "\n",
    "for i in query:\n",
    "    i.delete_instance()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "2021-04-04\n2021-03-28\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "输出工作周报\n",
    "'''\n",
    "\n",
    "from dblib import *\n",
    "\n",
    "output_report()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "新项目:兴山县峡口港有限责任公司授信项目已记录，由汪宇管理，目前状态为1.营销阶段\n"
     ]
    }
   ],
   "source": [
    "'''\n",
    "新增项目\n",
    "'''\n",
    "\n",
    "from dblib import *\n",
    "# 企业名称\n",
    "name = '兴山县峡口港有限责任公司'\n",
    "# 客户经理名称\n",
    "member = '汪宇'\n",
    "\n",
    "date = date(2021, 4, 9)\n",
    "\n",
    "#--------------------------\n",
    "proj = new_project(name_ = name, member_ = member)\n",
    "try:\n",
    "    with database.atomic():\n",
    "        proj.date = date\n",
    "        proj.save()\n",
    "except:\n",
    "    pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "湖北瑞泰工程管理有限公司的新方案是：综合授信2000万元\n"
     ]
    }
   ],
   "source": [
    "'''\n",
    "完善项目信息 —— 改方案 \n",
    "'''\n",
    "# 要修改的项目名称\n",
    "corp_name = '湖北瑞泰工程管理有限公司'\n",
    "# 新方案\n",
    "new_scheme = '综合授信2000万元'\n",
    "\n",
    "#--------------------------------------\n",
    "proj = Project.get(Project.name == corp_name)\n",
    "proj.change_scheme(new_scheme)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "宜昌华西矿业有限责任公司项目当前状态：2.调查阶段\n"
     ]
    }
   ],
   "source": [
    "'''\n",
    "完善项目信息 —— 改状态 \n",
    "'''\n",
    "# 要修改的项目名称\n",
    "corp_name = '宜昌华西矿业有限责任公司'\n",
    "# 状态调整的方向\n",
    "direction = '+'\n",
    "\n",
    "#----------------------------------------------------\n",
    "from dblib import *\n",
    "proj = Project.get(Project.name == corp_name)\n",
    "if direction == '+':\n",
    "    proj.forward()\n",
    "elif direction == '-':\n",
    "    proj.back()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "宜昌苏鹏科技有限公司项目的备注信息：2021-04-04:已办理开户，评级后需上报一行一策;2021-04-18:已完成开户评级，更新报告后本周上分行评议会；\n"
     ]
    }
   ],
   "source": [
    "'''\n",
    "完善项目信息 —— 改进度\n",
    "'''\n",
    "# 要修改的项目名称\n",
    "corp_name = '宜昌苏鹏科技有限公司'\n",
    "# 新进度\n",
    "info = '已完成开户评级，更新报告后本周上分行评议会；'\n",
    "\n",
    "#----------------------------------------------------\n",
    "from dblib import *\n",
    "proj = Project.get(Project.name == corp_name)\n",
    "proj.flash_info(info)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "在存量的表里面增加一个字段，不要随便用。不要随便用。不要随便用。\n",
    "'''\n",
    "\n",
    "from dblib import *\n",
    "from playhouse.migrate import *\n",
    "from datetime import date\n",
    "\n",
    "# 给现成的表新增一个 field，使用migrate()\n",
    "# 使用完之后，如果你需要再去调用数据库，可以直接使用 pwiz 生成Model。\n",
    "\n",
    "\n",
    "# migrator = SqliteMigrator(database)\n",
    "# date_field = DateField(default=date(2000,1,1))\n",
    "# migrate(migrator.add_column('Project','date', date_field))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "删除一个project实例\n",
    "'''\n",
    "\n",
    "from dblib import *\n",
    "\n",
    "query = Project.select().where(Project.name == 'xxx')\n",
    "for q in query:\n",
    "    q.delete_instance()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "output_type": "error",
     "ename": "ProjectDoesNotExist",
     "evalue": "<Model: Project> instance matching query does not exist:\nSQL: SELECT \"t1\".\"id\", \"t1\".\"name\", \"t1\".\"member_id\", \"t1\".\"scheme\", \"t1\".\"info\", \"t1\".\"flag\", \"t1\".\"date\", \"t1\".\"tag\" FROM \"project\" AS \"t1\" WHERE (\"t1\".\"name\" = ?) LIMIT ? OFFSET ?\nParams: ['湖北祥云(集团)化工股份有限公司 ', 1, 0]",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mIndexError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[1;32me:\\worktools\\venv\\lib\\site-packages\\peewee.py\u001b[0m in \u001b[0;36mget\u001b[1;34m(self, database)\u001b[0m\n\u001b[0;32m   6875\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 6876\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mclone\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdatabase\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   6877\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mIndexError\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32me:\\worktools\\venv\\lib\\site-packages\\peewee.py\u001b[0m in \u001b[0;36m__getitem__\u001b[1;34m(self, item)\u001b[0m\n\u001b[0;32m   4267\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfill_cache\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mitem\u001b[0m \u001b[1;32mif\u001b[0m \u001b[0mitem\u001b[0m \u001b[1;33m>\u001b[0m \u001b[1;36m0\u001b[0m \u001b[1;32melse\u001b[0m \u001b[1;36m0\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 4268\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrow_cache\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mitem\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   4269\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mIndexError\u001b[0m: list index out of range",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[1;31mProjectDoesNotExist\u001b[0m                       Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-28-f192a688e377>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      7\u001b[0m \u001b[1;31m#---------------------------------------------\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      8\u001b[0m \u001b[1;32mfrom\u001b[0m \u001b[0mdblib\u001b[0m \u001b[1;32mimport\u001b[0m \u001b[1;33m*\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 9\u001b[1;33m \u001b[0mproj\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mProject\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mProject\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mname\u001b[0m \u001b[1;33m==\u001b[0m \u001b[0mcorp_name\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     10\u001b[0m \u001b[1;32mif\u001b[0m \u001b[0mproj\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtag\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34m''\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     11\u001b[0m     \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'项目名称:'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mproj\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32me:\\worktools\\venv\\lib\\site-packages\\peewee.py\u001b[0m in \u001b[0;36mget\u001b[1;34m(cls, *query, **filters)\u001b[0m\n\u001b[0;32m   6429\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mfilters\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   6430\u001b[0m             \u001b[0msq\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0msq\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfilter\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m**\u001b[0m\u001b[0mfilters\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 6431\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0msq\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   6432\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   6433\u001b[0m     \u001b[1;33m@\u001b[0m\u001b[0mclassmethod\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32me:\\worktools\\venv\\lib\\site-packages\\peewee.py\u001b[0m in \u001b[0;36mget\u001b[1;34m(self, database)\u001b[0m\n\u001b[0;32m   6877\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mIndexError\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   6878\u001b[0m             \u001b[0msql\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparams\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mclone\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msql\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 6879\u001b[1;33m             raise self.model.DoesNotExist('%s instance matching query does '\n\u001b[0m\u001b[0;32m   6880\u001b[0m                                           \u001b[1;34m'not exist:\\nSQL: %s\\nParams: %s'\u001b[0m \u001b[1;33m%\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   6881\u001b[0m                                           (clone.model, sql, params))\n",
      "\u001b[1;31mProjectDoesNotExist\u001b[0m: <Model: Project> instance matching query does not exist:\nSQL: SELECT \"t1\".\"id\", \"t1\".\"name\", \"t1\".\"member_id\", \"t1\".\"scheme\", \"t1\".\"info\", \"t1\".\"flag\", \"t1\".\"date\", \"t1\".\"tag\" FROM \"project\" AS \"t1\" WHERE (\"t1\".\"name\" = ?) LIMIT ? OFFSET ?\nParams: ['湖北祥云(集团)化工股份有限公司 ', 1, 0]"
     ]
    }
   ],
   "source": [
    "'''\n",
    "展示项目完整信息\n",
    "'''\n",
    "\n",
    "corp_name = '湖北祥云(集团)化工股份有限公司 '\n",
    "\n",
    "#---------------------------------------------\n",
    "from dblib import *\n",
    "proj = Project.get(Project.name == corp_name)\n",
    "if proj.tag == '':\n",
    "    print('项目名称:', proj.name)\n",
    "    print('客户经理:', proj.member.name)\n",
    "    print('融资方案:', proj.scheme)\n",
    "    print('启动时间:', proj.date)\n",
    "    print('项目进展:', proj.info)\n",
    "else:\n",
    "    print(proj.name, '已暂停')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from dblib import *\n",
    "\n",
    "output_project()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ]
}